﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Data;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using System.Collections;
using DataAccess.ProxyConnections;
using Helper;

namespace DataAccess.DBConnections
{
    public class DBConnectionsEngine
    {
        #region Attributes
        private OracleConnection Conn = new OracleConnection();
        private DataTable dt = new DataTable();
        private OracleDataAdapter da = new OracleDataAdapter();
        private OracleDataReader dr;
        private OracleCommand cm;
        private OracleTransaction tr;
        private ArrayList arlSqlCommand = new ArrayList();
        #endregion

        #region Constructor
        public DBConnectionsEngine()
        {
            this.Conn = new OracleConnection();
            this.dt = new DataTable();
        }
        #endregion
        
        #region Execute Query
        public int ExecuteNonQuery(string pSql, OracleConnection pConn)
        {
            int result = 0;
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.tr = pConn.BeginTransaction();
                this.cm = new OracleCommand(pSql, pConn);
                result = this.cm.ExecuteNonQuery();
                this.tr.Commit();
            }
            catch (Exception ex)
            {
                new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();

            }
            finally
            {
                pConn.Close();
            }
            return result;
        }
        public int ExecuteNonQuery(OracleConnection pConn)
        {
            int result = 0;
            string command = "";
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();

                this.cm = pConn.CreateCommand();
                this.tr = pConn.BeginTransaction();

                if (this.arlSqlCommand.Count > 0)
                {
                    foreach (object item in this.arlSqlCommand)
                    {
                        command = item.ToString();
                        this.cm.CommandText = item.ToString();
                        result += this.cm.ExecuteNonQuery();
                    }
                    this.tr.Commit();
                }

            }
            catch (Exception ex)
            {
                new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
            finally
            {
                pConn.Close();
                pConn.Dispose();
                this.tr.Dispose();
                this.cm.Dispose();
                this.arlSqlCommand.Clear();
            }
            return result;
        }
        public object ExecuteScalar(string pSql, OracleConnection pConn)
        {
            object countRecord = 0;
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();

                this.cm = new OracleCommand(pSql, pConn);
                countRecord = this.cm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
            finally
            {
                pConn.Close();
            }
            return countRecord;
        }
        public OracleCommand GetProcedureCommand(string pProcName, OracleConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.cm = new OracleCommand(pProcName, pConn);
                this.cm.CommandType = CommandType.StoredProcedure;
                return this.cm;
            }
            catch (Exception ex)
            {
                new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
        }
        public OracleCommand GetFunctionCommand(string pProcName, OracleConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.cm = new OracleCommand(pProcName, pConn);
                this.cm.CommandType = CommandType.StoredProcedure;
                return this.cm;
            }
            catch (Exception ex)
            {
                new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
        }
        public int ManyExecuteNonQuery(OracleConnection pConn, params string[] pSql)
        {
            int result = 0;
            try
            {
                if (pSql.Length > 0)
                {
                    if (pConn.State == ConnectionState.Open) pConn.Close();
                    pConn.Open();
                    this.tr = pConn.BeginTransaction();
                    for (int i = 0; i <= pSql.Length - 1; i++)
                    {
                        if (pSql[i] != null)
                        {
                            this.cm = new OracleCommand(pSql[i], pConn);
                            result = result + this.cm.ExecuteNonQuery();
                        }
                    }
                    this.tr.Commit();
                }

            }
            catch (Exception ex)
            {
                result = 0;
                new FileHandle().insertErrorLogFile(ex.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();
            }
            finally
            {
                pConn.Close();
            }
            return result;
        }
        public int ManyExecuteNonQuery(OracleConnection pConn, ArrayList pSql)
        {

            int result = 0;
            try
            {

                if (pSql.Count > 0)
                {
                    if (pConn.State == ConnectionState.Open) pConn.Close();
                    pConn.Open();
                    this.tr = pConn.BeginTransaction();
                    foreach (string tmpSql in pSql)
                    {

                        if (tmpSql != null)
                        {
                            this.cm = new OracleCommand(tmpSql, pConn);
                            result = result + this.cm.ExecuteNonQuery();
                        }
                    }
                    this.tr.Commit();
                }

            }
            catch (Exception e)
            {
                result = 0;
                new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                this.tr.Rollback();
                throw new Exception();

            }
            finally
            {
                pConn.Close();
            }
            return result;
        }
        public DataTable GetDataTable(string pSql, OracleConnection pConn)
        {
            this.dt = new DataTable();
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();
                this.da = new OracleDataAdapter(pSql, pConn);
                this.da.Fill(this.dt);
            }
            catch (Exception e)
            {
                new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            finally
            {
                pConn.Close();
            }
            return this.dt;
        }
        public DataTable GetDataTable(string strSql, Hashtable h, OracleConnection Conn)
        {
            OracleCommand cmd = Conn.CreateCommand();
            DataTable dt = new DataTable();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            cmd.BindByName = true;
            try
            {
                foreach (string key in h.Keys)
                {
                    cmd.Parameters.Add(key, h[key]);
                }
                OracleDataAdapter adap = new OracleDataAdapter(cmd);
                adap.Fill(dt);

            }
            catch (Exception e)
            {
                new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            return dt;
        }
        public OracleDataReader GetDataReader(string pSql, OracleConnection pConn)
        {
            try
            {
                if (pConn.State == ConnectionState.Open) pConn.Close();
                pConn.Open();

                this.cm = new OracleCommand(pSql, pConn);
                this.dr = this.cm.ExecuteReader();
            }
            catch (Exception e)
            {
                new FileHandle().insertErrorLogFile(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                throw new Exception();
            }
            return this.dr;
        }
       
        #endregion
    }
}
